package HostCluster;

require Exporter;

use Pg;
use strict;

use include::Param;

our @ISA =qw(Exporter);

our @EXPORT_OK = qw( );
our @EXPORT = @EXPORT_OK;

our $ver;
our %self;

sub new {
    my $class=shift;
    my $iam = { };
    $self{table}='host_cluster';
    $self{pk}='id';
    $self{id}=$_[0];
    $self{database_fields}={ };
    $self{modified_fields}={ };

    $self{sql}=Pg::connectdb(Param::get_conn_string('local_conn'));
    if ($self{sql}->status != PGRES_CONNECTION_OK) {
	print "HostCluster Connect Error: ".$self{sql}->errorMessage."\n";
	exit;
    }
    my $res=$self{sql}->exec("SELECT * FROM ".$self{table}." WHERE ".$self{pk}."=".$self{id});
    if ($res->resultStatus != PGRES_TUPLES_OK) {
	print $self{sql}->errorMessage;
	exit;
    }
    my @obj=$res->fetchrow;
    for (my $f=0;$f < $res->nfields;$f++) {
	$self{database_fields}{$res->fname($f)}=$obj[$f];
	$self{modified_fields}{$res->fname($f)}=0;
    }
    $ver=$self{database_fields}{pg_version};
    $ver=~s/\.//g;

    bless($iam,$class);
    return $iam;
}

###############################################

sub set_prod_conn {
    my $res=$self{sql}->exec("SELECT get_conn_string(".$self{id}.") AS prod_conn_string");
    my ($conn_string)=$res->fetchrow;
    $self{prod_sql}=Pg::connectdb($conn_string);
    if ($self{prod_sql}->status != PGRES_CONNECTION_OK) {
	print "HostCluster Prod Connect Error: ".$self{prod_sql}->errorMessage."\n";
	exit;
    }
}

#sub pg_version2int {
#    return $_[0]=~s/\.//g;
#}

###############################################
=comment
sub get_field {
    shift;
    foreach my $field (keys %{$self{database_fields}}) {
	if ($field eq $_[0]) {
	    return $self{database_fields}{$field};
	}
    }
    return undef;
}

sub get_all_fields {
    shift;
    return $self{database_fields};
}

sub get_id {
    shift;
    return $self{id};
}
=cut
sub get_version {
    shift;
    return $ver;
}


sub get_databases {
    my $db_res=$self{sql}->exec("SELECT * FROM database_name WHERE hc_id=".$self{id}." AND observable AND alive");
    my @ret;
    while (my @dbs=$db_res->fetchrow) {
	my %struct;
	for (my $f=0; $f < $db_res->nfields; $f++) {
	    $struct{$db_res->fname($f)}=$dbs[$f];
	}
	push(@ret,\%struct);
    }
    return @ret;
}

sub discover_functions {
    shift;
    foreach my $db (get_databases()) {
	my %database=%{$db};
	my $prod_conn_string_res=$self{sql}->exec("SELECT get_conn_string(".$self{id}.",".$database{id}.") AS prod_conn_string");
	my ($string)=$prod_conn_string_res->fetchrow;
	my $db_prod_sql=Pg::connectdb($string);
	if ($db_prod_sql->status != PGRES_CONNECTION_OK) {
	    print "Database Connection Error. Database ".$database{db_name}." host ".$self{database_fields}{hostname}." ".$self{prod_sql}->errorMessage."\n";
	    exit;
	}
	my $sn_res=$self{sql}->exec("SELECT * from schema_name WHERE alive AND observable AND dn_id=".$database{id});
	while (my @sch=$sn_res->fetchrow) {
	    my %schema;
	    for (my $s=0;$s < $sn_res->nfields;$s++) {
		$schema{$sn_res->fname($s)}=$sch[$s];
	    }
	    my $local_fnc_res=$self{sql}->exec("SELECT * FROM function_name WHERE alive AND sn_id=".$schema{id});
	    my @exist_functions;
	    while (my @fnc=$local_fnc_res->fetchrow) {
		my %l_func;
		for (my $lf=0;$lf < $local_fnc_res->nfields;$lf++) {
		    $l_func{$local_fnc_res->fname($lf)}=$fnc[$lf];
		}
		push(@exist_functions,\%l_func);
	    }
	    my $prod_fnc_q="SELECT p.oid AS pro_oid,p.proname AS funcname,p.proretset,t.typname,l.lanname
		FROM pg_proc p
		LEFT JOIN pg_namespace n ON n.oid = p.pronamespace
		JOIN pg_type t ON p.prorettype=t.oid
		JOIN pg_language l ON p.prolang=l.oid
		WHERE (p.prolang <> (12)::oid)
		AND n.oid=".$schema{obj_oid};

	    my $prod_fnc_res=$db_prod_sql->exec($prod_fnc_q);
#print $prod_fnc_q."\n".$schema{id}."\n";
	    my @prod_functions;
	    while (my @prod_fnc=$prod_fnc_res->fetchrow) {
#print $prod_fnc[0]."\n";
		my %p_fnc;
		for (my $pf=0; $pf < $prod_fnc_res->nfields;$pf++) {
		    $p_fnc{$prod_fnc_res->fname($pf)}=$prod_fnc[$pf];
#print $prod_fnc_res->fname($pf)."\t=".$prod_fnc[$pf]."\n";
		}
		push (@prod_functions,\%p_fnc);
		foreach (@exist_functions) {
#print ${$_}{func_name}."\t".${$_}{id}."\t".${$_}{pro_oid}."\n";
		    if (${$_}{pro_oid} == $p_fnc{pro_oid}) {
#print "Going to undef ".${$_}{id}."\t".${$_}{func_name}."\n";
			pop(@prod_functions);
			${$_}{id}=undef;
			last;
		    }
		}
	    }
	    foreach (@exist_functions) {
		if (defined(${$_}{id})) {
		    $self{sql}->exec("UPDATE function_name SET alive='f' WHERE id=".${$_}{id});
		}
	    }
	    foreach (@prod_functions) {
		$self{sql}->exec("INSERT INTO function_name (sn_id,pro_oid,proretset,func_name,prorettype,prolang) VALUES (".
		$schema{id}.",".${$_}{pro_oid}.",'".${$_}{proretset}."','".${$_}{funcname}."','".${$_}{typname}."','".${$_}{lanname}."')");
#print "INSERT INTO function_name (sn_id,pro_oid,proretset,func_name,prorettype,prolang) VALUES (".
#		$schema{id}.",".${$_}{pro_oid}.",'".${$_}{proretset}."','".${$_}{funcname}."','".${$_}{typname}."','".${$_}{lanname}."')\n";
	    }
	}
    }
}


sub discover_tables {
    shift;
    foreach my $db (get_databases()) {
	my %database=%{$db};
	my $prod_conn_string_res=$self{sql}->exec("SELECT get_conn_string(".$self{id}.",".$database{id}.") AS prod_conn_string");
	my ($string)=$prod_conn_string_res->fetchrow;
	my $db_prod_sql=Pg::connectdb($string);
	if ($db_prod_sql->status != PGRES_CONNECTION_OK) {
	    print "Database Connection Error. Database ".$database{db_name}." host ".$self{database_fields}{hostname}." ".$self{prod_sql}->errorMessage."\n";
	    exit;
	}
	my $sn_res=$self{sql}->exec("SELECT * from schema_name WHERE alive AND observable AND dn_id=".$database{id});
	while (my @sch=$sn_res->fetchrow) {
	    my %schema;
	    for (my $s=0;$s < $sn_res->nfields;$s++) {
		$schema{$sn_res->fname($s)}=$sch[$s];
	    }
	    my $local_tbl_res=$self{sql}->exec("SELECT * FROM table_name WHERE alive AND sn_id=".$schema{id});
#print "SELECT * FROM table_name WHERE alive AND sn_id=".$schema{id}."\n";
	    my @exist_tables;

	    while (my @tbl=$local_tbl_res->fetchrow) {
		my %l_table;
		for (my $t=0;$t < $local_tbl_res->nfields;$t++) {
		    $l_table{$local_tbl_res->fname($t)}=$tbl[$t];
		}
		push(@exist_tables,\%l_table);
	    }
#print "Found ".$#exist_tables." records\n";


	    my $prod_tbl_q="SELECT r.oid,r.relname,r.reltoastrelid,
    CASE WHEN h.inhrelid IS NULL THEN 'f'::boolean ELSE 't'::boolean END AS has_parent,
    CASE WHEN i.indexrelid IS NULL THEN 0::int ELSE (SELECT COUNT(1) FROM pg_index WHERE indrelid=r.oid)::int END AS indexes
    FROM pg_class r
    LEFT JOIN pg_inherits h ON r.oid=h.inhrelid
    LEFT JOIN pg_index i ON r.oid=i.indrelid
    WHERE r.relkind='r'
    AND r.relnamespace=".$schema{obj_oid}.
    " GROUP BY 1,2,3,4,5
    ORDER BY 1,2,3,4,5";
#print $prod_tbl_q."\n";
	    my $prod_tbl_res=$db_prod_sql->exec($prod_tbl_q);
    
	    my @prod_tables;
	    while (my @prod_tbl=$prod_tbl_res->fetchrow) {
		my %p_table;
		for (my $pt=0;$pt < $prod_tbl_res->nfields;$pt++) {
		    $p_table{$prod_tbl_res->fname($pt)}=$prod_tbl[$pt];
		}
		push(@prod_tables,\%p_table);
######################################################
		foreach my $exist_table (@exist_tables) {
		    if (${$exist_table}{obj_oid} == $p_table{oid}) {

# TOAST ##############################################################################
			if ($p_table{reltoastrelid}) {
#print "Executing Prod Tables. ".${$exist_table}{id}."\n";
			    my $toast_res=$self{sql}->exec("SELECT 1 FROM table_toast_name WHERE tn_id=".${$exist_table}{id});
			    if (!$toast_res->fetchrow) {
				my $toast_ti_res=$db_prod_sql->exec("SELECT t.oid AS t_relid,t.relname AS t_relname,t.reltoastidxid AS t_i_relid,i.relname AS t_i_relname
				    FROM pg_class t
				    INNER JOIN pg_class r ON r.reltoastrelid=t.oid
				    INNER JOIN pg_class i ON t.reltoastidxid=i.oid
				    WHERE t.oid=".$p_table{reltoastrelid});
				my ($t_relid,$t_relname,$t_i_relid,$t_i_relname)=$toast_ti_res->fetchrow;
				$self{sql}->exec("INSERT INTO table_toast_name (tn_id,obj_oid,tbl_name) VALUES (".${$exist_table}{id}.",".$t_relid.",'".$t_relname."')");
				$self{sql}->exec("INSERT INTO index_toast_name (tn_id,obj_oid,idx_name) VALUES (".${$exist_table}{id}.",".$t_i_relid.",'".$t_i_relname."')");
			    }
			}

######################################################################################
# INDEXES ############################################################################
			my $index_res=$self{sql}->exec("SELECT * FROM index_name WHERE tn_id=".${$exist_table}{id});
			my @exist_indexes;
			while (my @index_row=$index_res->fetchrow) {
			    my %ind;
			    for (my $if=0;$if < $index_res->nfields; $if++) {
				$ind{$index_res->fname($if)}=$index_row[$if];
			    }
			    push(@exist_indexes,\%ind);
			}
			my @prod_indexes;
			if ($p_table{indexes}) {
			    my $prod_ind_res=$db_prod_sql->exec("SELECT i.indexrelid,c.relname,i.indisunique,i.indisprimary
				FROM pg_index i
				JOIN pg_class c ON i.indexrelid=c.oid
				WHERE i.indrelid=".$p_table{oid});
			    while (my @prod_index=$prod_ind_res->fetchrow) {
				my %p_index;
				for (my $pif=0; $pif < $prod_ind_res->nfields; $pif++) {
				    $p_index{$prod_ind_res->fname($pif)}=$prod_index[$pif];
				}
				push(@prod_indexes,\%p_index);
				foreach my $exist_index (@exist_indexes) {
				    if ($p_index{indexrelid} == ${$exist_index}{obj_oid}) {
					${$exist_index}{id}=undef;
					pop(@prod_indexes);
					last;
				    }
				}
			    }
			}
			foreach (@exist_indexes) {
			    if (defined ${$_}{id}) {
			        $self{sql}->exec("UPDATE index_name SET alive='f' WHERE id=".${$_}{id});
			    }
			}
			foreach (@prod_indexes) {
			    $self{sql}->exec("INSERT INTO index_name (tn_id,obj_oid,is_unique,is_primary,idx_name) VALUES (".
			    ${$exist_table}{id}.",".${$_}{indexrelid}.",'".${$_}{indisunique}."','".${$_}{indisprimary}."','".${$_}{relname}."')");
			}
######################################################################################
#print "Going to undef ".${$exist_table}{id}."\t".${$exist_table}{tbl_name}."\n";
			${$exist_table}{id}=undef;
			pop(@prod_tables);
			last;
		    }
		}
	    }
	    foreach (@exist_tables) {
		if (defined ${$_}{id}) {
		    $self{sql}->exec("UPDATE table_name SET alive = 'f' WHERE id=".${$_}{id});
		}
	    }
	    foreach (@prod_tables) {
		my $new_table_res=$self{sql}->exec("INSERT INTO table_name (sn_id,obj_oid,has_parent,tbl_name) VALUES (".$schema{id}.",".${$_}{oid}.",'".${$_}{has_parent}."','".${$_}{relname}."') RETURNING id");
#print "INSERT INTO table_name (sn_id,obj_oid,has_parent,tbl_name) VALUES (".$schema{id}.",".${$_}{oid}.",'".${$_}{has_parent}."','".${$_}{relname}."') RETURNING id\n";
		my ($table_id)=$new_table_res->fetchrow;
		if (${$_}{reltoastrelid}) {
## TOAST #############################
		    my $toast_ti_q="SELECT t.oid AS t_relid,t.relname AS t_relname,t.reltoastidxid AS t_i_relid,i.relname AS t_i_relname
			FROM pg_class t
			INNER JOIN pg_class r ON r.reltoastrelid=t.oid
			INNER JOIN pg_class i ON t.reltoastidxid=i.oid
			WHERE t.oid=".${$_}{reltoastrelid};
		    my $toast_ti_res=$db_prod_sql->exec($toast_ti_q);
		    my ($t_relid,$t_relname,$t_i_relid,$t_i_relname)=$toast_ti_res->fetchrow;
		    $self{sql}->exec("INSERT INTO table_toast_name (tn_id,obj_oid,tbl_name) VALUES (".$table_id.",".$t_relid.",'".$t_relname."')");
		    $self{sql}->exec("INSERT INTO index_toast_name (tn_id,obj_oid,idx_name) VALUES (".$table_id.",".$t_i_relid.",'".$t_i_relname."')");
		}
		if (${$_}{indexes}) {
## INDEX #############################
		    my $new_ind_res=$db_prod_sql->exec("SELECT i.indexrelid,c.relname,i.indisunique,i.indisprimary
			FROM pg_index i
			JOIN pg_class c ON i.indexrelid=c.oid
			WHERE i.indrelid=".${$_}{oid});
		    while (my ($indexrelid,$relname,$indisunique,$indisprimary)=$new_ind_res->fetchrow) {
			$self{sql}->exec("INSERT INTO index_name (tn_id,obj_oid,is_unique,is_primary,idx_name) VALUES (".
			    $table_id.",".$indexrelid.",'".$indisunique."','".$indisprimary."','".$relname."')");
		    }
		}
	    }
	}
    }
}



sub write_db_stat {
    shift;
    if ($self{database_fields}{track_counts} eq 't') {
	if (!$self{prod_sql}) {
	    set_prod_conn();
	}
	foreach my $db (get_databases()) {
	    my %database=%{$db};
	    my $stat_q="SELECT
pg_database_size(oid) AS db_size,
pg_stat_get_db_xact_commit(oid) AS xact_commit,
pg_stat_get_db_xact_rollback(oid) AS xact_rollback,
pg_stat_get_db_blocks_fetched(oid) AS blks_fetch,
pg_stat_get_db_blocks_hit(oid) AS blks_hit,
pg_stat_get_db_tuples_returned(oid) AS tup_returned,
pg_stat_get_db_tuples_fetched(oid) AS tup_fetched,
pg_stat_get_db_tuples_inserted(oid) AS tup_inserted,
pg_stat_get_db_tuples_updated(oid) AS tup_updated,
pg_stat_get_db_tuples_deleted(oid) AS tup_deleted
FROM pg_database
WHERE oid =".$database{obj_oid};
	    my $stat_res=$self{prod_sql}->exec($stat_q);
	    if ($stat_res->resultStatus != PGRES_TUPLES_OK) {
		print "Error Getting database statistic for database ".$database{db_name}." host ".$self{database_fields}{hostname}."\n";
		exit;
	    }
	    my @dbs=$stat_res->fetchrow;
	    my %db_stat;
	    for (my $f=0;$f < $stat_res->nfields;$f++) {
		$db_stat{$stat_res->fname($f)}=$dbs[$f];
	    }
	    my $ins_q="INSERT INTO database_stat VALUES (".$database{id}.",".$_[0].",
".$db_stat{db_size}.",
".$db_stat{xact_commit}.",
".$db_stat{xact_rollback}.",
".$db_stat{blks_fetch}.",
".$db_stat{blks_hit}.",
".$db_stat{tup_returned}.",
".$db_stat{tup_fetched}.",
".$db_stat{tup_inserted}.",
".$db_stat{tup_updated}.",
".$db_stat{tup_deleted}.")";
	    my $ins_res=$self{sql}->exec($ins_q);
	    if ($ins_res->resultStatus != PGRES_COMMAND_OK) {
		print "Error Inserting database stat for database ".$database{db_name}." host ".$self{database_fields}{hostname}."\n";
		exit;
	    }
	}
    }
}

sub write_bg_stat {
    shift;
    if ($self{database_fields}{track_counts} eq 't') {
	if (!$self{prod_sql}) {
	    set_prod_conn();
	}
	my $stat_res=$self{prod_sql}->exec("SELECT
pg_stat_get_bgwriter_timed_checkpoints() AS checkpoints_timed,
pg_stat_get_bgwriter_requested_checkpoints() AS checkpoints_req,
pg_stat_get_bgwriter_buf_written_checkpoints() AS buffers_checkpoint,
pg_stat_get_bgwriter_buf_written_clean() AS buffers_clean,
pg_stat_get_bgwriter_maxwritten_clean() AS maxwritten_clean,
pg_stat_get_buf_written_backend() AS buffers_backend,
pg_stat_get_buf_alloc() AS buffers_alloc");
	if ($stat_res->resultStatus != PGRES_TUPLES_OK) {
	    print "Error Getting bgwriter stat for host ".$self{database_fields}{hostname}."\n";
	    exit;
	}
	my @bg_stat=$stat_res->fetchrow;
	my %bgw;
	for (my $f=0;$f < $stat_res->nfields;$f++) {
	    $bgw{$stat_res->fname($f)}=$bg_stat[$f];
	}
	my $ins_res=$self{sql}->exec("INSERT INTO bgwriter_stat VALUES (
".$self{id}.",".$_[0].",
".$bgw{checkpoints_timed}.",
".$bgw{checkpoints_req}.",
".$bgw{buffers_checkpoint}.",
".$bgw{buffers_clean}.",
".$bgw{maxwritten_clean}.",
".$bgw{buffers_backend}.",
".$bgw{buffers_alloc}.")");
	if ($ins_res->resultStatus != PGRES_COMMAND_OK) {
	    print "Error Inserting bgwriter stat for host ".$self{database_fields}{hostname}."\n";
	    exit;
	}
    }
}

sub write_function_stat {
    shift;
    if ($self{database_fields}{track_functions} ne 'none') {
	my $func_stat_q="
SELECT
COALESCE(pg_stat_get_function_calls(oid),0) AS func_calls,
COALESCE((pg_stat_get_function_time(oid)),0) AS total_time,
COALESCE((pg_stat_get_function_self_time(oid)),0) AS self_time
FROM pg_proc p
WHERE oid=";
    }
}

1;


